package da;

import data.Village;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.ResourceBundle;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author groep 11
 * @date 2007-2008
 */
public class VillageDA {

    private ResourceBundle bundle;
    private static VillageDA instance = null;

    private VillageDA() {
        bundle = ResourceBundle.getBundle("da.dbconsts");
    }

    public static VillageDA getInstance() {
        if (instance == null) {
            instance = new VillageDA();
        }
        return instance;
    }

    // <editor-fold desc="SELECTS" defaultstate="collapsed">
    /**
     * Fetches all villages from the DB
     * @return villages
     */
    public Vector<Village> getAllVillages() {
        Vector<Village> villages = null;
        Village village = null;

        String sql = bundle.getString("getAllVillages");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                ResultSet rs = prepstat.executeQuery();

                villages = new Vector<Village>();
                while (rs.next()) {
                    village = new Village(rs.getInt("village_id"), rs.getString("name"),
                            rs.getInt("owner_id"), rs.getInt("location_x"),
                            rs.getInt("location_y"));
                    villages.add(village);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(VillageDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getAllVillages error -->" + ex.toString());
        }

        return villages;
    }

    
    /**
     * Fetches all initialized (not -1 -1) villages from the DB
     * @return villages
     */
    public Vector<Village> getAllInitializedVillages() {
        Vector<Village> villages = null;
        Village village = null;

        String sql = bundle.getString("getAllInitializedVillages");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                ResultSet rs = prepstat.executeQuery();

                villages = new Vector<Village>();
                while (rs.next()) {
                    village = new Village(rs.getInt("village_id"), rs.getString("name"),
                            rs.getInt("owner_id"), rs.getInt("location_x"),
                            rs.getInt("location_y"));
                    villages.add(village);
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(VillageDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getInitializedVillages error -->" + ex.toString());
        }

        return villages;
    }
    /**
     * Fetches a village by its name.
     * Returns null if not found.
     * @param villageName
     * @return village
     */
    public Village getVillageByName (String villageName) {
        Village village = null;
        
        String sql = bundle.getString("getVillageByName");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setString(1, villageName);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    village = new Village(rs.getInt("village_id"), rs.getString("name"),
                            rs.getInt("owner_id"), rs.getInt("location_x"),
                            rs.getInt("location_y"));
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(VillageDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getVillageByName error -->" + ex.toString());
        }
        
        return village;
    }
    
    
    /**
     * Fetches a village by user name.
     * Returns null if not found.
     * @param username
     * @return village
     */
    public Village getVillageByUsername(String username) {
        Village village = null;

        String sql = bundle.getString("getVillageByUsername");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                prepstat.setString(1, username);

                ResultSet rs = prepstat.executeQuery();
                while (rs.next()) {
                    village = new Village(rs.getInt("village_id"), rs.getString("name"),
                            rs.getInt("owner_id"), rs.getInt("location_x"),
                            rs.getInt("location_y"));
                }

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(VillageDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("getVillageByUsername error -->" + ex.toString());
        }
        return village;

    }

    // </editor-fold>
    // <editor-fold desc="INSERTS" defaultstate="collapsed">
    /**
     * Adds a new village to the DB
     * @param village
     */
    public void addVillage(Village village) {
        String sql = bundle.getString("addVillage");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Add data in table "village"
                prepstat.setString(1, village.getName());
                prepstat.setInt(2, village.getOwnerId());
                prepstat.setInt(3, village.getLocationX());
                prepstat.setInt(4, village.getLocationY());
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(VillageDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("addVillage error -->" + ex.toString());
        }
    }
    // </editor-fold>
    // <editor-fold desc="UPDATES" defaultstate="collapsed">
    
    /**
     * Sets the location of a village
     * @param villageId
     * @param x
     * @param y 
     */
    public void updateVillageLocation(int villageId, int x, int y) { 
        String sql = bundle.getString("editVillageLocation");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Updates data in table "village"
                prepstat.setInt(1, x);
                prepstat.setInt(2, y);
                prepstat.setInt(3, villageId);
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(VillageDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("updateVillageLocation error -->" + ex.toString());
        }
    }

    // </editor-fold>
    // <editor-fold desc="DELETES" defaultstate="collapsed">
    /**
     * Removes the village with given id from the DB.
     * @param villageId
     */
    public void removeVillage(int villageId) {
        String sql = bundle.getString("removeVillage");
        Connection dbcon = null;
        try {
            dbcon = DatabaseConnection.getInstance().getConnection();
            PreparedStatement prepstat = dbcon.prepareStatement(sql);
            try {
                // Delete from table "village"
                prepstat.setInt(1, villageId);
                prepstat.executeUpdate();

            } finally {
                prepstat.close();
                dbcon.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(VillageDA.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("removeVillage error -->" + ex.toString());
        }
    }
    // </editor-fold>
}
